﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace MyWeb.Data
{
    public class UserController : SqlDataProvider
    {
        #region [User_CheckLogin]

        public DataTable User_CheckLogin(string user, string pass)
        {
            string query = "select [rule],[status] from [User] where username='" + user + "' and password='" + pass +
                           "'";
            return GetTable(query);
        }

        #endregion

        #region [User_GetByAll]

        public DataTable User_GetByAll()
        {
            return GetDataTable("sp_User_GetByAll");
        }

        #endregion

        #region [User_GetById]

        public DataTable User_GetById(int Id)
        {
            var p1 = new SqlParameter("@Id", Id);
            return GetDataTable("sp_User_GetById", p1);
        }

        #endregion

        #region [User_Insert]

        public void User_Insert(UserInfo user)
        {
            var p2 = new SqlParameter("@Name", user.Name);
            var p3 = new SqlParameter("@Username", user.Username);
            var p4 = new SqlParameter("@Password", user.Password);
            var p5 = new SqlParameter("@Rule", user.Rule);
            var p6 = new SqlParameter("@Birthday", user.Birthday);
            var p7 = new SqlParameter("@Gender", user.Gender);
            var p8 = new SqlParameter("@Email", user.Email);
            var p9 = new SqlParameter("@Address", user.Address);
            var p10 = new SqlParameter("@Phone", user.Phone);
            var p11 = new SqlParameter("@CreateDate", user.CreateDate);
            var p12 = new SqlParameter("@Status", user.Status);

            ExecuteStoreProcedure("sp_User_Insert", p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12);
        }

        #endregion

        #region [User_Update]

        public void User_Update(UserInfo user)
        {
            var p1 = new SqlParameter("@Id", user.Id);
            var p2 = new SqlParameter("@Name", user.Name);
            var p3 = new SqlParameter("@Username", user.Username);
            var p4 = new SqlParameter("@Password", user.Password);
            var p5 = new SqlParameter("@Rule", user.Rule);
            var p6 = new SqlParameter("@Birthday", user.Birthday);
            var p7 = new SqlParameter("@Gender", user.Gender);
            var p8 = new SqlParameter("@Email", user.Email);
            var p9 = new SqlParameter("@Address", user.Address);
            var p10 = new SqlParameter("@Phone", user.Phone);
            var p11 = new SqlParameter("@CreatDate", user.CreateDate);
            var p12 = new SqlParameter("@Status", user.Status);

            ExecuteStoreProcedure("sp_User_Update", p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12);
        }

        #endregion

        #region [User_UpdateNoPass]

        public void User_UpdateNoPass(UserInfo user)
        {
            var p1 = new SqlParameter("@Id", user.Id);
            var p2 = new SqlParameter("@Name", user.Name);
            var p3 = new SqlParameter("@Username", user.Username);
            var p5 = new SqlParameter("@Rule", user.Rule);
            var p6 = new SqlParameter("@Birthday", user.Birthday);
            var p7 = new SqlParameter("@Gender", user.Gender);
            var p8 = new SqlParameter("@Email", user.Email);
            var p9 = new SqlParameter("@Address", user.Address);
            var p10 = new SqlParameter("@Phone", user.Phone);
            var p11 = new SqlParameter("@CreatDate", user.CreateDate);
            var p12 = new SqlParameter("@Status", user.Status);
            ExecuteStoreProcedure("sp_User_UpdateNoPass", p1, p2, p3, p5, p6, p7, p8, p9, p10, p11, p12);
        }

        #endregion

        #region [User_Delete]

        public void User_Delete(int id)
        {
            var p1 = new SqlParameter("@Id", id);
            ExecuteStoreProcedure("sp_User_Delete", p1);
        }

        #endregion

        #region [User_ActiveStatus]

        public void User_ActiveStatus(int id, bool status)
        {
            var p1 = new SqlParameter("@id", id);
            var p2 = new SqlParameter("@status", status);
            ExecuteStoreProcedure("sp_User_ActiveStatus", p1, p2);
        }

        #endregion

        #region [User_GetUserId]

        public string User_GetUserId(string username)
        {
            string ten = "";
            Con = GetCon();
            var cmd = new SqlCommand("select id from [User] where username='" + username + "'", Con);
            //con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                ten = dr[0].ToString();
                Con.Close();
                return ten;
            }
            Con.Close();
            return ten;
        }

        #endregion

        #region [User_PasswordChange]

        public void User_PasswordChange(string tennguoidung, string matkhaucu, string matkhaumoi)
        {
            string sql = string.Format("Update [User] set password='{0}' where username='{1}' and password='{2}'",
                                       matkhaumoi, tennguoidung, matkhaucu);
            ExecuteNonQuery(sql);
        }

        #endregion

        #region [User_LosePassword]

        public void User_LosePassword(string username, string password)
        {
            var p1 = new SqlParameter("@Username", username);
            var p2 = new SqlParameter("@Password", password);
            ExecuteStoreProcedure("sp_User_LosePassword", p1, p2);
        }

        #endregion

        #region [User_GetUsername]

        public DataTable User_GetUsername(string email)
        {
            SqlParameter p1 = new SqlParameter("@email", email);
            return GetDataTable("sp_User_GetUsername", p1);
        }

        #endregion

        #region [User_GetId]

        public int User_GetId()
        {
            return Int32.Parse(GetTopDataCell("[User]", "Id", "Id"));
        }

        #endregion
    }
}